//package com.LLL.product.dao;
//
//import com.LLL.DBUtill;
//import com.LLL.Util;
//import com.LLL.customer.model.Product;
//import com.LLL.product.model.ShoppingCar;
//
//import java.sql.*;
//import java.util.ArrayList;
//import java.util.List;
//
//public class ProductDao {
//    /**
//     * 获取商品列表
//     */
//    public List<Product> getProductList() {
//        List<Product> list = new ArrayList<>();
//        //1、获取连接
//        Connection conn = DBUtill.getConnection();
//        //2、准备SQL
//        String sql = "select * from tb_product order by create_time desc limit 10";
//        //3\声明操作对象和结果集集对象
//        PreparedStatement ps = null;
//        ResultSet rs = null;
//        //4、获取操作对象
//        try {
//            ps = conn.prepareStatement(sql);
//            //5、执行SQL获取结果集
//            rs = ps.executeQuery();
//            //6、解析对象
//            while (rs.next()) {
//                //7、创建对象并设置数据
//                Product product = new Product();
//                product.setId(rs.getInt("id"));
//                product.setCategory_main_id(rs.getString("category_main_id"));
//                product.setCategory_branch_id(rs.getString("category_branch_id"));
//                product.setName(rs.getString("name"));
//                product.setProducing_area(rs.getString("producing_area"));
//                product.setDescription(rs.getString("description"));
//                product.setCreate_time(rs.getString("create_time"));
//                product.setMarket_price(rs.getDouble("market_price"));
//                product.setSell_price(rs.getDouble("sell_price"));
//                product.setProduct_amount(rs.getInt("product_amount"));
//                product.setPicture(rs.getString("picture"));
//                product.setDiscount(rs.getString("discount"));
//                //8、将商品信息添加到list中
//                list.add(product);
//                //9、将对象置为空，让GC尽快回收内存
//                product = null;
//            }
//        } catch (SQLException e) {
//            throw new RuntimeException(e);
//        }
//        return list;
//    }
//
//    /**
//     * 根据商品编号来获取商品信息
//     */
//    public Product getProductById(String id) {
//        Connection conn = DBUtill.getConnection();
//        String sql = "select * from tb_product where id = ?";
//        PreparedStatement ps = null;
//        ResultSet rs = null;
//        try {
//            ps = conn.prepareStatement(sql);
//            ps.setString(1, id);
//            rs = ps.executeQuery();
//            if (rs.next()) {
//                Product product = new Product();
//                product.setId(rs.getInt("id"));
//                product.setCategory_main_id(rs.getString("category_main_id"));
//                product.setCategory_branch_id(rs.getString("category_branch_id"));
//                product.setName(rs.getString("name"));
//                product.setProducing_area(rs.getString("producing_area"));
//                product.setDescription(rs.getString("description"));
//                product.setCreate_time(rs.getString("create_time"));
//                product.setMarket_price(rs.getDouble("market_price"));
//                product.setSell_price(rs.getDouble("sell_price"));
//                product.setProduct_amount(rs.getInt("product_amount"));
//                product.setPicture(rs.getString("picture"));
//                product.setDiscount(rs.getString("discount"));
//                return product;
//
//            }
//        } catch (SQLException e) {
//            throw new RuntimeException(e);
//        } finally {
//            DBUtill.clossResource(conn, ps, rs);
//        }
//        return null;
//    }
//
//    /**
//     * 将商品添加到购物车
//     */
//    public int addCar(String customerId, String productId, String number) {
//        Connection conn = DBUtill.getConnection();
//        String sql = "INSERT INTO `tb_car` (`customerid`,`productid`,`number`) VALUES (?,?,?)";
//        PreparedStatement ps = null;
//        try {
//            ps = conn.prepareStatement(sql);
//            ps.setString(1, customerId);
//            ps.setString(2, productId);
//            ps.setString(3, number);
//            int result = ps.executeUpdate();
//            return result;
//        } catch (SQLException e) {
//            throw new RuntimeException(e);
//        } finally {
//            DBUtill.clossResource(conn, ps, null);
//        }
//    }
//
//    /**
//     * 将商品更新购物车的商品数量
//     */
//    public int updateCar(String customerId, String productId, String number) {
//        //连接数据库
//        Connection conn = DBUtill.getConnection();
//        String sql = "UPDATE `tb_car` set `number` = ? where `customerid` = ? and `productid` = ?";
//        PreparedStatement ps = null;
//        try {
//            ps = conn.prepareStatement(sql);
//            ps.setString(1, number);
//            ps.setString(2, customerId);
//            ps.setString(3, productId);
//            int result = ps.executeUpdate();
//            return result;
//        } catch (SQLException e) {
//            throw new RuntimeException(e);
//        } finally {
//            DBUtill.clossResource(conn, ps, null);
//        }
//    }
//
//    /**
//     * 判断当前用户是否已添加此商品
//     */
//    public int isExists(String customerId, String productId) {
//        Connection conn = DBUtill.getConnection();
//        String sql = "select * from tb_car where `customerid` =? and `productid` =?";
//        PreparedStatement ps = null;
//        ResultSet rs = null;
//        try {
//            ps = conn.prepareStatement(sql);
//            ps.setString(1, customerId);
//            ps.setString(2, productId);
//            rs = ps.executeQuery();
//            if (rs.next()) {
//                int number = rs.getInt("number");
//                return number;
//            }
//        } catch (SQLException e) {
//            throw new RuntimeException(e);
//        } finally {
//            DBUtill.clossResource(conn, ps, rs);
//        }
//        return 0;
//    }
//
//    /**
//     * 根据用户编号查询这个用户加购了哪些商品，返回购物车列表
//     */
//    public List<ShoppingCar> getShoppingCarInfo(String customerId) {
//        Connection conn = DBUtill.getConnection();
//        String sql = "SELECT * from tb_car JOIN tb_product on tb_car.productid = tb_product.id WHERE tb_car.customerid=?";
//        PreparedStatement ps = null;
//        ResultSet rs = null;
//        List<ShoppingCar> list = new ArrayList<>();
//        try {
//            ps = conn.prepareStatement(sql);
//            rs = ps.executeQuery();
//            while (rs.next()) {
//                ShoppingCar shoppingCar = new ShoppingCar();
//                shoppingCar.setCustomerId(rs.getInt("customerid"));
//                shoppingCar.setProductId(rs.getInt("productid"));
//                shoppingCar.setDescription(rs.getString("description"));
//                shoppingCar.setName(rs.getString("name"));
//                shoppingCar.setPicture(rs.getString("picture"));
//                shoppingCar.setNumber(rs.getInt("number"));
//                shoppingCar.setSell_price(rs.getDouble("sell_price"));
//                list.add(shoppingCar);
//                shoppingCar = null;
//            }
//            return list;
//        } catch (SQLException e) {
//            throw new RuntimeException(e);
//        } finally {
//            DBUtill.clossResource(conn, ps, rs);
//        }
//    }
//
//    public int addOrder(String phone, List<ShoppingCar> list) {
//        Connection conn = DBUtill.getConnection();
//        String sql = "INSERT INTO `tb_order` (`order_id`,`mobile`) VALUE(?,?)";
//        PreparedStatement ps = null;
//        String orderId = Util.createOrderId();
//        int n = 0;
//        try {
//            ps = conn.prepareStatement(sql);
//            ps.setString(1, orderId);
//            ps.setString(2, phone);
//            n = ps.executeUpdate();
//        } catch (SQLException e) {
//            throw new RuntimeException(e);
//        } finally {
//            DBUtill.clossResource(conn, ps, null);
//        }
//        if (n > 0) {
//            Connection conn2 = DBUtill.getConnection();
//            String sql2 = "INSERT INTO `tb_order_item`(`order_id`,`product_id`,`product_name`,`product_price`,`amount`) VALUES(?,?,?,?,?)";
//            PreparedStatement ps2 = null;
//            try {
//                ps2 = conn2.prepareStatement(sql2);
//                for (int i = 0; i < list.size(); i++) {
//                        ShoppingCar shoppingCar = list.get(i);
//                        ps2.addBatch();
//                        ps2.setString(1, orderId);
//                        ps2.setInt(2, shoppingCar.getProductId());
//                        ps2.setString(3, shoppingCar.getName());
//                        ps2.setDouble(4,shoppingCar.getSell_price());
//                        ps2.setInt(5, shoppingCar.getNumber());
//                }
//            } catch (SQLException e) {
//                throw new RuntimeException(e);
//            }
//        }
//        return 1;
//    }
//}
//ProductDao页面

package com.LLL.product.dao;

import com.LLL.DBUtill;
import com.LLL.Util;
import com.LLL.customer.model.Product;
import com.LLL.product.model.ShoppingCar;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ProductDao {
    /**获取商品列表*/
    public List<Product> getProductList() {
        List<Product> list = new ArrayList<>();
        //1、获取连接
        Connection conn = DBUtill.getConnection();
        //2、准备SQL
        String sql = "select * from tb_product order by create_time desc limit 10";
        //3、声明操作对象和结果集集对象
        PreparedStatement ps = null;
        ResultSet rs = null;
        //4、获取操作对象
        try {
            ps = conn.prepareStatement(sql);
            //5、执行SQL获取结果集
            rs = ps.executeQuery();
            //6、解析对象
            while (rs.next()) {
                //7、创建对象并设置数据
                Product product = new Product();
                product.setId(rs.getInt("id"));
                product.setCategory_main_id(rs.getString("category_main_id"));
                product.setCategory_branch_id(rs.getString("category_branch_id"));
                product.setName(rs.getString("name"));
                product.setProducing_area(rs.getString("producing_area"));
                product.setDescription(rs.getString("description"));
                product.setCreate_time(rs.getString("create_time"));
                product.setMarket_price(rs.getDouble("market_price"));
                product.setSell_price(rs.getDouble("sell_price"));
                product.setProduct_amount(rs.getInt("product_amount"));
                product.setPicture(rs.getString("picture"));
                product.setDiscount(rs.getString("discount"));
                //8、将商品信息添加到list中
                list.add(product);
                //9、将对象置为空，让GC尽快回收内存
                product = null;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return list;
    }
    /**根据商品编号来获取商品信息*/
    public Product getProductById(String id) {
        Connection conn = DBUtill.getConnection();
        String sql = "select * from tb_product where id = ?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1, id);
            rs = ps.executeQuery();
            if (rs.next()) {
                Product product = new Product();
                product.setId(rs.getInt("id"));
                product.setCategory_main_id(rs.getString("category_main_id"));
                product.setCategory_branch_id(rs.getString("category_branch_id"));
                product.setName(rs.getString("name"));
                product.setProducing_area(rs.getString("producing_area"));
                product.setDescription(rs.getString("description"));
                product.setCreate_time(rs.getString("create_time"));
                product.setMarket_price(rs.getDouble("market_price"));
                product.setSell_price(rs.getDouble("sell_price"));
                product.setProduct_amount(rs.getInt("product_amount"));
                product.setPicture(rs.getString("picture"));
                product.setDiscount(rs.getString("discount"));
                return product;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtill.clossResource(conn, ps, rs);
        }
        return null;
    }
    /**将商品添加到购物车*/
    public int addCar(String customerId, String productId, String number) {
        //连接数据库
        Connection conn = DBUtill.getConnection();
        //准备sql
        String sql = "INSERT INTO `tb_car` (`customerid`,`productid`,`number`) VALUES (?,?,?)";
        //获取操作对象
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1, customerId);
            ps.setString(2, productId);
            ps.setString(3, number);
            int result = ps.executeUpdate();
            return result;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtill.clossResource(conn, ps, null);
        }
    }
    /**将商品更新购物车的商品数量*/
    public int updateCar(String customerId, String productId, String number) {
        //连接数据库
        Connection conn = DBUtill.getConnection();
        //准备sql
        String sql = "UPDATE `tb_car` set `number` = ? where `customerid` = ? and `productid` = ?";
        //获取操作对象
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1, number);
            ps.setString(2, customerId);
            ps.setString(3, productId);
            int result = ps.executeUpdate();
            return result;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtill.clossResource(conn, ps, null);
        }
    }
    /**判断当前用户是否已添加此商品*/
    public int isExists(String customerId, String productId) {
        Connection conn = DBUtill.getConnection();
        String sql = "select * from tb_car where `customerid` =? and `productid` =?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1, customerId);
            ps.setString(2, productId);
            rs = ps.executeQuery();
            if (rs.next()) {
                int number = rs.getInt("number");
                return number;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtill.clossResource(conn, ps, rs);
        }
        return 0;
    }
    /**根据用户编号查询这个用户加购了哪些商品，返回购物车列表*/
    public List<ShoppingCar> getShoppingCarInfo(String customerId) {
        Connection conn = DBUtill.getConnection();
        String sql = "SELECT * from tb_car JOIN tb_product on tb_car.productid = tb_product.id WHERE tb_car.customerid=?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<ShoppingCar> list = new ArrayList<>();
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1, customerId);
            rs = ps.executeQuery();
            while (rs.next()) {
                ShoppingCar shoppingCar = new ShoppingCar();
                shoppingCar.setCustomerId(rs.getInt("customerid"));
                shoppingCar.setProductId(rs.getInt("productid"));
                shoppingCar.setDescription(rs.getString("description"));
                shoppingCar.setName(rs.getString("name"));
                shoppingCar.setPicture(rs.getString("picture"));
                shoppingCar.setNumber(rs.getInt("number"));
                shoppingCar.setSell_price(rs.getDouble("sell_price"));
                list.add(shoppingCar);
                shoppingCar = null;
            }
            return list;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtill.clossResource(conn, ps, rs);
        }
    }
    /** 根据会员联系方式，及购物车列表来生成订单*/
    public int addOrder(String phone, String total_price, List<ShoppingCar> list) {
        Connection conn = DBUtill.getConnection();
        String sql = "INSERT INTO `tb_order` (`order_id`,`mobile`,`total_price`) VALUE(?,?,?)";
        PreparedStatement ps = null;
        String orderId = Util.createOrderId();
        int n = 0;
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1, orderId);
            ps.setString(2, phone);
            ps.setString(3, total_price);
            n = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtill.clossResource(null, ps, null);
        }
        if (n > 0) {
            Connection conn2 = DBUtill.getConnection();
            String sql2 = "INSERT INTO `tb_order_item`(`order_id`,`product_id`,`product_name`,`product_price`,`amount`) VALUES(?,?,?,?,?)";
            PreparedStatement ps2 = null;
            try {
                ps2 = conn2.prepareStatement(sql2);
                for (int i = 0; i < list.size(); i++) {
                    ShoppingCar shoppingCar = list.get(i);
                    ps2.setString(1, orderId);
                    ps2.setInt(2, shoppingCar.getProductId());
                    ps2.setString(3, shoppingCar.getName());
                    ps2.setDouble(4,shoppingCar.getSell_price());
                    ps2.setInt(5, shoppingCar.getNumber());
                    //添加批处理
                    ps2.addBatch();
                }
                //执行批处理
                ps2.executeBatch();
                return 1;
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }finally {
                DBUtill.clossResource(conn, ps2, null);
            }
        }
        return 0;
    }
}
